if(typeof FMP == 'undefined') { FMP = {}; }

FMP.database = {
	db: null,
	open: function(callback)
	{
		var dbSize = 30 * 1024 * 1024 * 1024;
		this.db = openDatabase('fmp', '', 'Songs data', dbSize);
		var that = this;
		
		if(this.db.version == "") {
		
			this.db.changeVersion("", "1", function() {
				that.createTables(callback);
			});
			
		} else if(this.db.version == "1") {
			this.db.changeVersion("1", "2", function() {
				that.query([
					{query: "CREATE TABLE IF NOT EXISTS channels (id INTEGER PRIMARY KEY AUTOINCREMENT, channel TEXT, last_opened DEFAULT (datetime('now', 'localtime')),  userID INTEGER, name TEXT, UNIQUE (channel))"},
					{query: "CREATE TABLE IF NOT EXISTS channels_songs (song_id INTEGER NOT NULL, channel_id INTEGER NOT NULL, PRIMARY KEY (song_id, channel_id))"},
				], callback);
			});
		} else if(this.db.version == "1.0") {
			this.db.changeVersion("1.0", "1", function() {
				that.dropTables(function() {
					that.createTables(callback);
				});					
			});
		} else if(this.db.version == "2") {
			callback();
		}
	},
	
	dropTables: function(callback) {
	
		this.query([
			{query: "DROP TABLE options", 			log: false},
			{query: "DROP TABLE library", 			log: false},
			{query: "DROP TABLE genres", 			log: false},
			{query: "DROP TABLE playlists", 		log: false},
			{query: "DROP TABLE playlists_songs", 	log: false},
			{query: "DROP TABLE genres_songs", 		log: false},
		], callback);	
	},
	
	createTables: function(callback) {
	
		this.query([
			/*
			{query: "DROP TABLE options", 			log: false},
			{query: "DROP TABLE library", 			log: false},
			{query: "DROP TABLE genres", 			log: false},
			{query: "DROP TABLE playlists", 		log: false},
			{query: "DROP TABLE playlists_songs", 	log: false},
			{query: "DROP TABLE genres_songs", 		log: false},
			*/
			
			{query: "CREATE TABLE IF NOT EXISTS options (userID INTEGER, meta_key TEXT, meta_value LONGTEXT, PRIMARY KEY (userID, meta_key))"},
			{query: "CREATE TABLE IF NOT EXISTS library (id INTEGER PRIMARY KEY AUTOINCREMENT, videoID TEXT, userID INTEGER, source TEXT, artist TEXT, title TEXT, last_played DATETIME DEFAULT NULL, times_played DEFAULT 0, added_at DEFAULT (datetime('now', 'localtime')), year INTEGER DEFAULT NULL, album TEXT, length TEXT, UNIQUE (userID, videoID, source))"},
			{query: "CREATE TABLE IF NOT EXISTS playlists (id INTEGER PRIMARY KEY AUTOINCREMENT, userID INTEGER, name TEXT)"},
			{query: "CREATE TABLE IF NOT EXISTS playlists_songs (id INTEGER PRIMARY KEY AUTOINCREMENT, song_id INTEGER NOT NULL, playlist_id INTEGER NOT NULL, position INTEGER DEFAULT NULL)"},
			{query: "CREATE TABLE IF NOT EXISTS genres (id INTEGER PRIMARY KEY AUTOINCREMENT, userID INTEGER, name TEXT)"},
			{query: "CREATE TABLE IF NOT EXISTS genres_songs (song_id INTEGER NOT NULL, genre_id INTEGER NOT NULL, PRIMARY KEY (song_id, genre_id))"},
			/*
			{query: squel.insert().into('library').set('source', 'y').set('id', '8UVNT4wvIGY').set("userID", FMP.data.userID).set("artist", "Gotye")
												  .set('title', 'Somebody that I used to know').set('length', '04:04')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'd9NF2edxy-M').set("userID", FMP.data.userID).set("artist", "Walk off the earth")
												  .set('title', 'Somebody that I used to know').set('length', '03:53')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'xFrGuyw1V8s').set("userID", FMP.data.userID).set("artist", "Abba")
												  .set('title', 'Dancing Queen').set('length', '03:52')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'rYEDA3JcQqw').set("userID", FMP.data.userID).set("artist", "Adele")
												  .set('title', 'Rolling in the deep').set('length', '03:54')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'VT1-sitWRtY').set("userID", FMP.data.userID).set("artist", "Avril Lavigne")
												  .set('title', 'Wish you were here').set('length', '03:47')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'unfzfe8f9NI').set("userID", FMP.data.userID).set("artist", "Abba")
												  .set('title', 'Mamma Mia').set('length', '03:31')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'bnVUHWCynig').set("userID", FMP.data.userID).set("artist", "Beyonce")
												  .set('title', 'Halo').set('length', '03:45')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', '1G4isv_Fylg').set("userID", FMP.data.userID).set("artist", "Coldplay")
												  .set('title', 'Paradise').set('length', '04:21')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'YVkUvmDQ3HY').set("userID", FMP.data.userID).set("artist", "Eminem")
												  .set('title', 'Without me').set('length', '05:00').set('album', 'Without you')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'Ahha3Cqe_fk').set("userID", FMP.data.userID).set("artist", "Katy Perry")
												  .set('title', 'The one that got away').set('length', '04:50')	},
			{query: squel.insert().into('library').set('source', 'y').set('id', 'n5c3tfvp4Lc').set("userID", FMP.data.userID).set("artist", "Nelly Furtado")
												  .set('title', 'Manos al aire').set('length', '03:30').set('year', 2011)	},
			{query: squel.insert().into('library').set('source', 'y').set('id', '1qmKk7fj9Ug').set("userID", FMP.data.userID).set("artist", "Dido")
												  .set('title', 'Hunter').set('length', '03:56').set('year', 2010)	},
			
			{query: squel.insert().into("genres").set("userID", FMP.data.userID).set('name', 'Rock') },
			{query: squel.insert().into("genres").set("userID", FMP.data.userID).set('name', 'Pop') },
			{query: squel.insert().into("genres").set("userID", FMP.data.userID).set('name', 'Dance') },
			{query: squel.insert().into("genres").set("userID", FMP.data.userID).set('name', 'Techno') },
			{query: squel.insert().into("genres").set("userID", FMP.data.userID).set('name', 'Trance') },
			{query: squel.insert().into("genres").set("userID", FMP.data.userID).set('name', 'Rap') },
			
			{query: squel.insert().into("playlists").set("userID", FMP.data.userID).set('name', 'Default') },
			
			{query: squel.insert().into("playlists_songs").set("song_id", "8UVNT4wvIGY").set('playlist_id', 1).set('position', "COALESCE( (SELECT MAX(position) FROM playlists_songs where playlist_id = 1)+1, 1)", true) },
			{query: squel.insert().into("playlists_songs").set("song_id", "1qmKk7fj9Ug").set('playlist_id', 1).set('position', "COALESCE( (SELECT MAX(position) FROM playlists_songs where playlist_id = 1)+1, 1)", true) },
			{query: squel.insert().into("playlists_songs").set("song_id", "bnVUHWCynig").set('playlist_id', 1).set('position', "COALESCE( (SELECT MAX(position) FROM playlists_songs where playlist_id = 1)+1, 1)", true) },
			{query: squel.insert().into("playlists_songs").set("song_id", "1qmKk7fj9Ug").set('playlist_id', 1).set('position', "COALESCE( (SELECT MAX(position) FROM playlists_songs where playlist_id = 1)+1, 1)", true) },
			{query: squel.insert().into("playlists_songs").set("song_id", "8UVNT4wvIGY").set('playlist_id', 1).set('position', "COALESCE( (SELECT MAX(position) FROM playlists_songs where playlist_id = 1)+1, 1)", true) },
			{query: squel.insert().into("playlists_songs").set("song_id", "1qmKk7fj9Ug").set('playlist_id', 1).set('position', "COALESCE( (SELECT MAX(position) FROM playlists_songs where playlist_id = 1)+1, 1)", true) },
			
			
			{query: squel.insert().into("genres_songs").set("song_id", "8UVNT4wvIGY").set('genre_id', 1) },
			{query: squel.insert().into("genres_songs").set("song_id", "bnVUHWCynig").set('genre_id', 1) },
			{query: squel.insert().into("genres_songs").set("song_id", "1qmKk7fj9Ug").set('genre_id', 2) },
			*/
			
		], callback);
	},
	
	loadPlaylist: function( playlist ) {
		var query = FMP.squel.select().from('library').where("userID", FMP.data.userID);
		query.field('*');
		query.field('playlists_songs.id as playlist_position_id');
		query.left_join("playlists_songs", null, "library.id = playlists_songs.song_id");
		query.where("playlists_songs.playlist_id", playlist);
		query.order('playlists_songs.position');
		
		FMP.database.query([{query: query, callback: function(res) {
			FMP.playingNow.add({replace: true, list: res.result, force: true});
		} }]);
	},
	
	loadGenre: function( genre ) {
		
		var query = FMP.squel.select().from('library').where("userID", FMP.data.userID);
		query.field('*');
		query.left_join("genres_songs", null, "library.id = genres_songs.song_id");
		query.where("genres_songs.genre_id", genre);
		query.order('library.added_at');
		
		FMP.database.query([{query: query, callback: function(res) {
			FMP.playingNow.add({replace: true, list: res.result, force: true});
		} }]);
	},
	
	loadChannel: function( channel ) {
		
		var query = FMP.squel.select().from('library').where("userID", FMP.data.userID);
		query.field('*');
		query.left_join("channels_songs", null, "library.id = channels_songs.song_id");
		query.where("channels_songs.channel_id", channel);
		query.order('library.added_at');
		
		FMP.database.query([{query: query, callback: function(res) {
			FMP.playingNow.add({replace: true, list: res.result, force: true});
		} }]);
	},
	
	
	getBackup: function(callback) {
	
		FMP.database.query([
			{ query: squel.select().from("options").where("userID", FMP.data.userID), callbackOpt: "options" },
			{ query: squel.select().from("playlists").where("userID", FMP.data.userID), callbackOpt: "playlists" },
			{ query: squel.select().from("genres").where("userID", FMP.data.userID), callbackOpt: "genres" },
			{ query: squel.select().from("library").where("userID", FMP.data.userID), callbackOpt: "library" },
			{ query: squel.select().from("playlists_songs").field('playlists_songs.*')
									.left_join('playlists', null, 'playlists.id = playlists_songs.playlist_id')
									.join("library", null, "library.id = playlists_songs.song_id")
									.where("playlists.userID", FMP.data.userID),
						callbackOpt: "playlists_songs"
			},
			{ query: squel.select().from("genres_songs").field('genres_songs.*')
									.left_join('genres', null, 'genres.id = genres_songs.genre_id')
									.join("library", null, "library.id = genres_songs.song_id")
									.where("genres.userID", FMP.data.userID), 
						callbackOpt: "genres_songs"
			}
		], callback);
	},
	
	updateOption: function(k,v) {
		v = JSON.stringify(v);
		
		console.log('Saving option: '+k + ' with ' + v);
		FMP.database.query([{query: FMP.squel.insert({replace: ' OR REPLACE '}).into('options').set('meta_key', k).set('meta_value', v).set('userID', FMP.data.userID)}]);
	},
	
	loadData: function() {
		FMP.database.query([
			{query: squel.select().from("playlists").where("userID", FMP.data.userID), callback: function(r) { FMP.data.populate('playlists', r); }},
			{query: squel.select().from("genres").where("userID", FMP.data.userID), callback: function(r) { FMP.data.populate('genres', r); }},
			{query: squel.select().from("channels").where("userID", FMP.data.userID), callback: function(r) { FMP.data.populate('channels', r); }},
			
			{query: squel.select().from("library").field("year as id, year as name").distinct().where("userID", FMP.data.userID).where('year is not null'), callback: function(r) { FMP.data.populate('years', r); }},
			{query: squel.select().from("library").field("album as id, album as name").distinct().where("userID", FMP.data.userID).where('album is not null'), callback: function(r) { FMP.data.populate('albums', r); }},
			{query: squel.select().from("options").where("userID", FMP.data.userID), callback: function(r) { FMP.data.options.populate(r); }},
		], FMP.mainStatus.ok);
	},
	
	query: function(queriesArr, callback, callbackOpt)
	{
		this.db.transaction(function(tx)
		{
			var results = [];
			
			function nextQuery() {
				if(queriesArr.length == 0) {
					if(typeof callback != 'undefined')
						callback(results, callbackOpt);
					return;
				}
				
				var i = 0;
				var query,arr;
				
				query = queriesArr[i].query;
				
				if(typeof query === 'object') {
					arr = query.getParams();
					query = query.toString();
				}
				else {
					arr = queriesArr[i].params || [];	
				}
				
				var qCallback = queriesArr[i].callback || false;
				var qLogError = (typeof queriesArr[i].log === 'undefined' || queriesArr[i].log === true ? true : false);
				var qCallbackOpt = queriesArr[i].callbackOpt || [];
				
				queriesArr = queriesArr.slice(1);
				
				console.log(query, arr);
				
				tx.executeSql(query, arr, function(tx, res)	{
					var result = {success: true, params: qCallbackOpt, query: query, query_params: arr};
					
					var res2 = [];
					
					try {
						if(res.insertId) {
							result['newId'] = res.insertId;
						}
					} catch(e) {}
					
					for(var i=0;i<res.rows.length;i++)
						res2.push(res.rows.item(i));
					
					result['result'] = res2;
					
					if(typeof results == 'undefined')
						results = [];
					
					if(typeof callback != 'undefined')
						results.push(result);
					
					if(qCallback) {
						qCallback(result);
					}
					
					nextQuery();
				},
				function(tx,e) 
				{
					var result = {success: false, error: e.message, result: [], params: qCallbackOpt, query: query, query_params: arr};
					
					if(typeof results == 'undefined')
						results = [];
					
					if(typeof callback != 'undefined')
						results.push(result);

					if(qCallback)
						qCallback(result);

					if(qLogError)
						FMP.error(e);
					
					nextQuery();
				});
			}
			
			nextQuery();
		});
	}
};